package entity;

import java.sql.*;
import java.util.ArrayList;
import java.util.Collection;
import javax.ejb.*;
import javax.naming.*;
import javax.sql.DataSource;
import transfer.Commodity;
import transfer.PopularityReport;

/**
 *
 * @author Sergey, yarko
 */
public class CommodityBean implements EntityBean {

    private EntityContext entityContext;
    private DataSource dataSource;
    private Commodity commodity = new Commodity();

    public Commodity getCommodity() {
        return commodity;
    }

    public void setCommodity(Commodity commodity) {
        this.commodity = commodity;
    }

    public void setEntityContext(EntityContext entityContext) throws EJBException {
        try {
            this.entityContext = entityContext;
            Context context = new InitialContext();
            dataSource = (DataSource) context.lookup("jdbc/ossystem");
        } catch (NamingException ex) {
            throw new EJBException("Does not find jdbc/ossystem");
        }
    }

    public void unsetEntityContext() {
        entityContext = null;
    }

    public void ejbRemove() throws RemoveException {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(
                    "DELETE FROM Commodity WHERE id_commodity = ?");
            statement.setInt(1, commodity.getIdCommodity().intValue());
            if (statement.executeUpdate() < 1) {
                throw new RemoveException("deleting error");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(connection, statement);
        }
    }

    public void ejbActivate() {
    }

    public void ejbPassivate() {
    }

    public void ejbLoad() throws EJBException {
        Integer primaryKey = (Integer) entityContext.getPrimaryKey();
        Connection connection = null;
        PreparedStatement statement = null;
        commodity = new Commodity();
        try {
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(
                    "SELECT id_category, name_commodity, price, description, image, popularity, out_of_production "
                    + "FROM Commodity WHERE id_commodity = ?");
            statement.setInt(1, primaryKey.intValue());
            ResultSet resultSet = statement.executeQuery();
            if (!resultSet.next()) {
                throw new NoSuchEntityException("Commodity with id = " + primaryKey + " not exists");
            }
            Context c = new InitialContext();
            CategoryLocalHome categoryHome = (CategoryLocalHome) c.lookup("java:comp/env/Category");
            CategoryLocal categoryLocal = categoryHome.findByPrimaryKey(new Integer(resultSet.getInt(1)));
            commodity.setCategory(categoryLocal.getCategory());
            commodity.setNameCommodity(resultSet.getString(2));
            commodity.setPrice(new Double(resultSet.getDouble(3)));
            commodity.setDescription(resultSet.getString(4));
            commodity.setImage(resultSet.getString(5));
            commodity.setPopularity(new Integer(resultSet.getInt(6)));
            commodity.setOutOfProduction(new Boolean(resultSet.getBoolean(7)));
            commodity.setIdCommodity(primaryKey);
        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (NamingException ex) {
            ex.printStackTrace();
        } catch (FinderException ex) {
            ex.printStackTrace();
        } finally {
            closeConnection(connection, statement);
        }
    }

    public void ejbStore() throws EJBException {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(
                    "UPDATE Commodity SET id_category = ?, name_commodity = ?, price = ?, description = ?, image = ?, popularity = ?, "
                    + "out_of_production = ? "
                    + "WHERE id_commodity = ?");
            statement.setInt(1, commodity.getCategory().getIdCategory().intValue());
            statement.setString(2, commodity.getNameCommodity());
            statement.setDouble(3, commodity.getPrice().doubleValue());
            statement.setString(4, commodity.getDescription());
            statement.setString(5, commodity.getImage());
            statement.setInt(6, commodity.getPopularity().intValue());
            statement.setBoolean(7, commodity.getOutOfProduction().booleanValue());
            statement.setInt(8, ((Integer) entityContext.getPrimaryKey()).intValue());
            statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(connection, statement);
        }
    }

    public java.lang.Integer ejbFindByPrimaryKey(Integer idCommodity) throws FinderException {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(
                    "SELECT id_commodity "
                    + "FROM Commodity WHERE id_commodity = ?");
            statement.setInt(1, idCommodity.intValue());
            ResultSet resultSet = statement.executeQuery();
            if (!resultSet.next()) {
                throw new FinderException("Commodity with primary key " + idCommodity + " is not exists");
            }
            return new Integer(resultSet.getInt(1));
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            closeConnection(connection, statement);
        }
    }

    public Collection ejbFindByPrice(Double aprice) throws FinderException {
        Connection connection = null;
        PreparedStatement statement = null;
        Collection list = new ArrayList() {
        };
        try {
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(
                    "SELECT id_commodity "
                    + "FROM Commodity WHERE price = ?");
            statement.setDouble(1, aprice.doubleValue());
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                list.add(new Integer(resultSet.getInt(1)));
            }
            if (list.isEmpty()) {
                throw new FinderException("Commodities with this price is not exists");
            }
            return list;
        } catch (SQLException e) {
            throw new EJBException("Error in SELECT");
        } finally {
            closeConnection(connection, statement);
        }
    }

    public Collection ejbFindAllGoods(int page, int count) throws FinderException {
        Connection connection = null;
        PreparedStatement statement = null;
        Collection list = new ArrayList() {
        };
        try {
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(
                    "SELECT id_commodity FROM "
                    + " (SELECT id_commodity, ROWNUM r FROM "
                    + " (SELECT id_commodity FROM commodity ORDER BY id_commodity DESC) "
                    + " WHERE ROWNUM <= ?) "
                    + " WHERE r > ? ");
            statement.setInt(1, page * count);
            statement.setInt(2, page * count - count);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                list.add(new Integer(resultSet.getInt(1)));
            }
            if (list.isEmpty()) {
                throw new FinderException("Commodities with this price is not exists");
            }
            return list;
        } catch (SQLException e) {
            throw new EJBException("Error in SELECT");
        } finally {
            closeConnection(connection, statement);
        }
    }

    public void ejbPostCreate(Commodity commodity) throws CreateException {
    }

    public java.lang.Integer ejbCreate(Commodity commodity) throws CreateException {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = dataSource.getConnection();
            Statement stat = connection.createStatement();
            Integer idCommodity = null;
            ResultSet resultSet = stat.executeQuery("select seq.nextval from dual");
            if (resultSet.next()) {
                idCommodity = new Integer(resultSet.getInt(1));
            }
            stat.close();
            statement = connection.prepareStatement("INSERT INTO Commodity"
                    + "(id_commodity, id_category, name_commodity, price, description, image, popularity, out_of_production) VALUES(?, ?, ?, ?, ?, ?, ?, ?)");
            statement.setInt(1, idCommodity.intValue());
            statement.setInt(2, commodity.getCategory().getIdCategory().intValue());
            statement.setString(3, commodity.getNameCommodity());
            statement.setDouble(4, commodity.getPrice().doubleValue());
            statement.setString(5, commodity.getDescription());
            statement.setString(6, commodity.getImage());
            statement.setInt(7, commodity.getPopularity().intValue());
            statement.setBoolean(8, commodity.getOutOfProduction().booleanValue());
            if (statement.executeUpdate() != 1) {
                throw new CreateException("Inserting error");
            }
            this.commodity = commodity;
            this.commodity.setIdCommodity(idCommodity);
            return idCommodity;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            closeConnection(connection, statement);
        }
    }

    private void closeConnection(Connection connection, Statement statement) {
        try {
            if (statement != null) {
                statement.close();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public Collection ejbFindByPopularity(int count) throws FinderException {
        Connection connection = null;
        PreparedStatement statement = null;
        Collection list = new ArrayList();
        try {
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(
                    "SELECT * FROM (SELECT id_commodity FROM Commodity WHERE NVL(out_of_production, 0) = 0 ORDER BY popularity DESC) "
                    + " WHERE rownum <= ?");
            statement.setInt(1, count);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                list.add(new Integer(resultSet.getInt(1)));
            }
            if (list.isEmpty()) {
                throw new FinderException("Commodities is not exists");
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            closeConnection(connection, statement);
        }
    }

    public Collection ejbFindByCategory(int category, int page, int count) throws FinderException {
        Connection connection = null;
        PreparedStatement statement = null;
        Collection list = new ArrayList();
        try {
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(
                    "SELECT id_commodity FROM "
                    + " (SELECT id_commodity, ROWNUM r FROM "
                    + " (SELECT id_commodity FROM commodity WHERE id_category = ? ORDER BY id_commodity DESC) "
                    + " WHERE ROWNUM <= ?) "
                    + " WHERE r > ? ");
            statement.setInt(1, category);
            statement.setInt(2, page * count);
            statement.setInt(3, page * count - count);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                list.add(new Integer(resultSet.getInt(1)));
            }
            if (list.isEmpty()) {
                throw new FinderException("Commodities is not exists");
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            closeConnection(connection, statement);
        }
    }

    public int ejbHomeAmountGoodsOfCategory(Integer idCategory) {
        Connection connection = null;
        PreparedStatement statement = null;
        int amount = 0;
        try {
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(
                    "SELECT COUNT(*) FROM commodity WHERE id_category = ? ");
            statement.setInt(1, idCategory.intValue());
            ResultSet resultSet = statement.executeQuery();
            if (resultSet.next()) {
                amount = resultSet.getInt(1);
            }
            return amount;
        } catch (SQLException e) {
            e.printStackTrace();
            return amount;
        } finally {
            closeConnection(connection, statement);
        }
    }

    public int ejbHomeAmountGoods() {
        Connection connection = null;
        PreparedStatement statement = null;
        int amount = 0;
        try {
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(
                    "SELECT COUNT(*) FROM commodity ");
            ResultSet resultSet = statement.executeQuery();
            if (resultSet.next()) {
                amount = resultSet.getInt(1);
            }
            return amount;
        } catch (SQLException e) {
            e.printStackTrace();
            return amount;
        } finally {
            closeConnection(connection, statement);
        }
    }

    public ArrayList ejbHomeGetPopularityReport(Integer idWarehouse, Integer startRow, Integer quantity) {
        Connection connection = null;
        PreparedStatement statement = null;
        String startPartQuery = "select wrname, comname, summ from (\n"
                                + "select wrname, comname, summ, rownum rn from (\n"
                                + "select warehouse.name_warehouse wrname, commodity.name_commodity comname, pop.sm summ\n"
                                + "from popular pop\n"
                                + "inner join commodity on pop.cm=commodity.id_commodity\n"     
                                + "inner join warehouse on pop.wr=warehouse.id_warehouse\n";
        String middlePartQuery = "order by sm desc, comname, wrname\n";

        try {
            String wherePart = "";
            if (idWarehouse != null) {
                wherePart = "where pop.wr = " + idWarehouse + "\n";
            }
            if (startRow == null) {
                startRow = new Integer(1);
            }
            String rowPart = ") ) where rn >= " + startRow;
            if (quantity != null) {
                rowPart += " and rn < " + (startRow.intValue() + quantity.intValue());
            }
            String query = startPartQuery + wherePart + middlePartQuery + rowPart;
            System.out.println("query = " + query);
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(query);
            ResultSet resultSet = statement.executeQuery();
            ArrayList result = new ArrayList();
            while (resultSet.next()) {
                PopularityReport reportItem = new PopularityReport();
                reportItem.warehouse = resultSet.getString(1);
                reportItem.commodity = resultSet.getString(2);
                reportItem.quantity = new Integer(resultSet.getInt(3));
                result.add(reportItem);
            }
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            closeConnection(connection, statement);
        }
    }
}
